#include "sqlmgr.h"

#include <qdatetime.h>
Sqlmgr* Sqlmgr::ptrstumysql=nullptr;//初始化，单例模式
Sqlmgr::Sqlmgr() {

}

Sqlmgr::~Sqlmgr()
{

}

void Sqlmgr::init()
{
    db=QSqlDatabase::addDatabase("QSQLITE");
    QString strPath=QCoreApplication::applicationDirPath()+"/db/data.db";
    qDebug()<<strPath;
    db.setDatabaseName(strPath);
    bool ok = db.open();
    if (ok){
        QMessageBox::information(NULL, "infor", "link success");
    }
    else {
        QMessageBox::information(NULL, "infor", "link failed");
        qDebug()<<"error open database because"<<db.lastError().text();
    }
}

bool Sqlmgr::login(QString strUser, QString strPassword, int &Userid)
{
    QSqlQuery q(db);
    QString strsql="select * from user where username=:username and password=:password";
    q.prepare(strsql);
    q.bindValue(":username",strUser);//使用参数化查询来保护数据库安全，参数化查询是一种将用户输入的值与SQL查询语句分离的方法
    q.bindValue(":password",strPassword);
    q.exec();
    bool ret=q.next();
    Userid =q.value(0).toUInt();

    return ret;
}

QVector<QStringList> Sqlmgr::getUser(QString strCondition)
{
    QSqlQuery q(db);
    QString strsql=QString("select *from user %1").arg(strCondition);
    // q.prepare(strsql);
    // q.bindValue("name",strCondition);
    QVector<QStringList> vec;
    q.exec(strsql);
        int iCols=q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i=0;i<iCols;i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
        }
    return vec;
}

void Sqlmgr::addUser(QVector<QStringList> v)
{
    db.transaction();
    QSqlQuery q(db);
    for(auto it:v)
    {
        QString strsql="insert into user VALUES(NULL,:username,:password,:auth,:grade,:department)";
        q.prepare(strsql);
        q.bindValue(":username",it[0]);
        q.bindValue(":password",it[1]);
        q.bindValue(":auth",it[2]);
        q.bindValue(":grade",it[3]);
        q.bindValue(":department",it[4]);
        // QString strsql=QString("insert into user VALUES(NULL,'%1','%2','%3','%4','%5')")
        //                      .arg(it[0])
        //                      .arg(it[1])
        //                      .arg(it[2])
        //                      .arg(it[3])
        //                      .arg(it[4]);
        q.exec();
    }
    db.commit();
}

void Sqlmgr::updateUser(QStringList ldata)
{
    QSqlQuery q(db);
    QString strsql="update user set username=:username,password=:passowrd,auth=:auth,grade=:grade,department=:department where id=:id";
    q.prepare(strsql);
    q.bindValue(":username",ldata[1]);
    q.bindValue(":passowrd",ldata[2]);
    q.bindValue(":auth",ldata[3]);
    q.bindValue(":grade",ldata[4]);
    q.bindValue(":department",ldata[5]);
    q.bindValue(":id",ldata[0]);
    q.exec();
}

void Sqlmgr::searchUser(QString strId)
{
    QSqlQuery q(db);
    QString strsql="select *from user where id=:id";
    q.prepare(strsql);
    q.bindValue(":id",strId);
    q.exec();
}

void Sqlmgr::delUser(QString strId)
{
    QSqlQuery q(db);
    QString strsql="delete from user where id=:id";
    q.prepare(strsql);
    q.bindValue(":id",strId);
    q.exec();
}

QVector<QStringList> Sqlmgr::getBook(QString strCondition)
{
    QSqlQuery q(db);
    QString strsql=QString("select *from Book %1").arg(strCondition);
    QVector<QStringList> vec;
    q.exec(strsql);
    int iCols=q.record().count();
    QStringList l;
    while(q.next())
    {
        l.clear();
        for(int i=0;i<iCols;i++)
        {
            l<<q.value(i).toString();
        }
        vec.push_back(l);
    }
    return vec;
}

void Sqlmgr::addBook(QVector<QStringList> v)
{
    if(0==v.size())
    {
        return;
    }
    auto ldata=v[0];
    QSqlQuery q(db);
        QString strsql="insert into Book values(NULL,:name,:count,:type1,:type2,:piace,'')";
        q.prepare(strsql);
        q.bindValue(":name",ldata[1]);
        q.bindValue(":count",ldata[2]);
        q.bindValue(":type1",ldata[3]);
        q.bindValue(":type2",ldata[4]);
        q.bindValue(":piace",ldata[5]);
            q.exec();
}

void Sqlmgr::updateBook(QStringList ldata)
{
    QSqlQuery q(db);
    QString strsql="update book set name=:name,count=:count,type1=:type1,type2=:type2,piace=:piace where bookid=:bookid";
    q.prepare(strsql);
    q.bindValue(":name",ldata[1]);
    q.bindValue(":count",ldata[2]);
    q.bindValue(":type1",ldata[3]);
    q.bindValue(":type2",ldata[4]);
    q.bindValue(":piace",ldata[5]);
    q.bindValue(":bookid",ldata[0]);
    q.exec();
}

QString Sqlmgr::searchBook(int id)
{
    QSqlQuery q(db);
    QString strsql="select name from Book where bookid=:bookid";
    q.prepare(strsql);
    q.bindValue(":bookid",id);
    q.exec();
    QString Bookname;
    if(q.next())
    {
        Bookname=q.value(0).toString();
    }
    return Bookname;
}


QString Sqlmgr::delBook(QString strID)
{
    QString strRet;
    QSqlQuery q(db);
    QString strsql=QString("delete from Book where bookid=%1").arg(strID);
    bool ret=q.exec(strsql);
    if(!ret)
    {
        strRet="删除失败,该图书在借阅中";
    }
    return strRet;
}

QString Sqlmgr::returnBook(QString strUserId, QString strBookId)
{
    QSqlQuery q(db);
    QString strSql="update book set count=count+1 where bookid=:bookid";
    q.prepare(strSql);
    q.bindValue(":bookid",strBookId);
    bool ret=q.exec();
    if(!ret)
    {
        qDebug() << "Error executing SQL query: " << q.lastError().text();
    }
    strSql="delete from record where bookid=:bookid and userid=:userid";
    q.prepare(strSql);
    q.bindValue(":bookid",strBookId);
    q.bindValue(":userid",strUserId);
    ret=q.exec();
    if(!ret)
    {
        qDebug() << "Error executing SQL query: " << q.lastError().text();
    }
    return QString("");
}

QString Sqlmgr::borrowBook(QString strUserId, QString strBookId)
{
   //实现图书借阅
    QSqlQuery q(db);
    QString strSql=QString("update book set count=count-1 where bookid=:bookid");
    q.prepare(strSql);
    q.bindValue(":bookid",strBookId);
    bool ret=q.exec();
   if(!ret)
    {
        qDebug() << "Error executing SQL query: " << q.lastError().text();
    }
   strSql="insert into record values(null,:bookid,:userid,:starttime,:endtime)";
    q.prepare(strSql);
    q.bindValue(":bookid",strBookId);
    q.bindValue(":userid",strUserId);
    qint64 currentTimeSecs = QDateTime::currentSecsSinceEpoch();
    QDateTime currentTime = QDateTime::fromSecsSinceEpoch(currentTimeSecs);
    QString currentDateTimeStr = currentTime.toString("yyyy-MM-dd hh:mm:ss");//转化日期格式

    qint64 dueTimeSecs = QDateTime::currentSecsSinceEpoch() + 3600 * 24 * 10;
    QDateTime dueTime = QDateTime::fromSecsSinceEpoch(dueTimeSecs);
    QString dueDateTimeStr = dueTime.toString("yyyy-MM-dd hh:mm:ss");
    q.bindValue(":starttime",currentDateTimeStr);
    q.bindValue(":endtime",dueDateTimeStr);
   if (q.exec()) {
       return QString(""); // 成功借阅，返回空字符串或其他信息
   } else {
       db.rollback(); // 回滚事务
       qDebug() << "Error executing SQL query: " << q.lastError().text();
       return QString("Error borrowing book"); // 返回错误信息
   }
}

QVector<QStringList> Sqlmgr::getRecord(QString strCondition)
{
    QSqlQuery q(db);
    QString strsql=QString("select *from record %1").arg(strCondition);
    QVector<QStringList> vec;
    q.exec(strsql);
    int iCols=q.record().count();
    QStringList l;
    while(q.next())
    {
        l.clear();
        for(int i=0;i<iCols;i++)
        {
            l<<q.value(i).toString();
        }
        vec.push_back(l);
    }
    return vec;
}

void Sqlmgr::clearRecord()
{
    QSqlQuery q(db);
    QString strsql=QString("delete from record;delete from sqlite_sequence where name='book';");
    q.exec(strsql);
}
